from collections import defaultdict
from typing import Any, List
from config.database import engine
from errors import BaseAPIException
from sqlalchemy import text
from sqlalchemy.orm import Session
import re
from utils.log_util import logger as log
from sqlalchemy.ext.asyncio import AsyncSession
from config.env import DataBaseConfig as dbcfg


def quote_sql_identifiers(sql):
    """
    为PostgreSQL SQL语句中的表名和字段名添加双引号，排除以下关键字后的字段：
    - ORDER BY, GROUP BY, HAVING
    - PARTITION BY, DISTINCT ON, ON CONFLICT, RETURNING, SET
    - WITH CUBE, WITH ROLLUP, GROUPING SETS
    已包含双引号的标识符保持不变。
    """
    # 需要排除处理的关键字/子句（后面的字段不添加引号）
    excluded_keywords = r'''
        ORDER\s+BY|
        GROUP\s+BY|
        HAVING|
        PARTITION\s+BY|
        DISTINCT\s+ON|
        ON\s+CONFLICT|
        RETURNING|
        SET|
        WITH\s+CUBE|
        WITH\s+ROLLUP|
        GROUPING\s+SETS
    '''

    # 其他SQL关键字（避免误判为标识符）
    keywords = (
        'SELECT|FROM|WHERE|AND|OR|JOIN|ON|AS|INSERT|UPDATE|DELETE|'
        'CREATE|ALTER|DROP|TABLE|DATABASE|INT|VARCHAR|TEXT|DATE|TIME|PRIMARY|KEY|FOREIGN|'
        'REFERENCES|NULL|NOT|LIKE|BETWEEN|IN|IS|VALUES|DISTINCT|ALL|ANY|UNION|INTERSECT|'
        'EXCEPT|CASE|WHEN|THEN|ELSE|END|TRUE|FALSE|LEFT|RIGHT|INNER|OUTER|FULL|CROSS|'
        'LIMIT|OFFSET|ROW_NUMBER|OVER|NULLS|FIRST|LAST|WITH|CUBE|ROLLUP|GROUPING'
    )

    # 分割SQL为“普通区域”和“排除关键字区域”
    split_pattern = rf'(?i)\b({excluded_keywords})\b'
    parts = re.split(split_pattern, sql, flags=re.VERBOSE)

    processed_parts = []

    for i in range(0, len(parts), 2):
        # 处理需要添加引号的“普通区域”
        current_part = parts[i]

        # 匹配未加引号的标识符（非关键字）
        identifier_pattern = rf'''
            (?<!["'])         # 前面无引号
            \b                # 单词边界
            (?!({keywords}|{excluded_keywords})\b)  # 不是关键字
            ([a-zA-Z_]        # 标识符起始（字母/下划线）
            [a-zA-Z0-9_]*     # 后续字符（字母/数字/下划线）
            (?:\.[a-zA-Z_][a-zA-Z0-9_]*)*) # 支持带点的限定名（如 schema.table.col）
            \b                # 单词边界
            (?!["'])          # 后面无引号
        '''

        # 替换函数：为标识符添加双引号
        def replace_with_quotes(match):
            identifier = match.group(2)
            if '.' in identifier:
                return '.'.join([f'"{part}"' for part in identifier.split('.')])
            return f'"{identifier}"'

        # 处理当前普通区域
        processed_part = re.sub(
            identifier_pattern,
            replace_with_quotes,
            current_part,
            flags=re.IGNORECASE | re.VERBOSE
        )
        processed_parts.append(processed_part)

        # 添加排除关键字（如果存在）
        if i + 1 < len(parts):
            processed_parts.append(parts[i + 1])

    return ''.join(processed_parts)


async def execute_async_sql(db:AsyncSession, sqls: List[str], isUpdate=False):
        async with db.begin():
            results = []
            # sql语法检查
            for sql in sqls:
                if dbcfg.db_type == 'postgresql':
                    sql = quote_sql_identifiers(sql)
                log.info(sql)
                version_tuple = engine.dialect.server_version_info
                log.info(f"PostgreSQL版本号元组：{version_tuple}")  # 输出示例：(15, 7)
                forbidden_pattern = re.compile("(delete|truncate)")
                if forbidden_pattern.match(sql):
                    raise BaseAPIException(detail="The dangerous sql is forbidden！！")
                result = await db.execute(text(sql))
                if isUpdate:
                    results.append("update success")
                else:
                    results.append(result.fetchall())
            return results


def getDataBySql(db: Session, sql: str, isUpdate=False, groupedBy=None):
    log.info(sql)
    if dbcfg.db_type == 'postgresql':
        sql = quote_sql_identifiers(sql)
    version_tuple = engine.dialect.server_version_info
    log.info(f"PostgreSQL版本号元组：{version_tuple}")  # 输出示例：(15, 7)
    forbidden_pattern = re.compile("(delete|truncate)")
    if forbidden_pattern.match(sql):
        raise BaseAPIException(detail="The dangerous sql is forbidden！！")

    if isUpdate:
        print(text(sql))
        db.execute(text(sql))
        result = "update success"
    else:
        result = db.execute(text(sql))

    log.info("groupedBy:{}",groupedBy)
    if groupedBy and not isUpdate:
        result = groupJsonData(result, groupedBy)
    else:
        result = result.all()
    # 在all()之后commit
    db.commit()
    return result


def groupJsonData(data, groupedBy):
    # 获取字段名
    columns = data.keys()
    # 转换为字典列表
    data = [dict(zip(columns, row)) for row in data.all()]
    grouped_data = defaultdict(list)
    # 按'groupedBy'字段分组
    for item in data:
        grouped_data[item[groupedBy]].append(item)
    # print(columns)
    return grouped_data
